﻿Public Class ClassPurchase
    Public Shared Function addPurchase(ByVal refI As String, ByVal supid As Integer, ByVal empid As Integer, ByVal ship As Double, ByVal exchange As Int16, ByVal amount As Double, ByVal disc As Int16, ByVal vat As Int16, ByVal total As Double, ByVal dates As Date, ByVal dgv As DataGridView, ByVal desc As String, ByVal status As Int16, ByVal pay As Double)
        Dim sql1 = <sql>
                    insert into tbl_purchase(referent_invoice,supplier_id,employee_id,shipping_rate,exchange_rate_detail_id,amount,discount,vat_rate,date,total,active)
                    value(@ref,@supid,@empid,@ship,@exchange,@amount,@discount,@vat,@date,@total,1)
                  </sql>
        Dim sql3 = <sql>
                    insert into tbl_purchase_payment(purchase_id,payment_date,pay_amount,exchange_rate_detail_id,description,pay_status,active)
                    value(@puid,@date,@amount,@exchange,@desc,@status,1)
                 </sql>
        Dim sql2 = <sql>
                    insert into tbl_purchase_detail(purchase_id,item_id,qty,price,discount,total,active)
                    value(@puid,@item,@qty,@price,@disc,@total,1) 
                 </sql>

        Dim sql4 = <sql>

                   </sql>

        Try
            Cmd.Begin()
            Cmd.Parameters("@ref") = refI
            Cmd.Parameters("@supid") = supid
            Cmd.Parameters("@empid") = empid
            Cmd.Parameters("@ship") = ship
            Cmd.Parameters("@exchange") = exchange
            Cmd.Parameters("@amount") = amount
            Cmd.Parameters("@discount") = disc
            Cmd.Parameters("@vat") = vat
            Cmd.Parameters("@date") = dates
            Cmd.Parameters("@total") = total
            Cmd.ExecuteNonQuery(sql1)

            Dim puid = Cmd.ExecuteScalar("select MAX(purchase_id) from tbl_purchase")



            Cmd.Parameters("@puid") = puid
            Cmd.Parameters("@date") = dates
            Cmd.Parameters("@amount") = pay
            Cmd.Parameters("@exchange") = exchange
            Cmd.Parameters("@desc") = desc
            Cmd.Parameters("@status") = status
            Cmd.ExecuteNonQuery(sql3)

            For i = 0 To dgv.Rows.Count - 1
                Cmd.Parameters("@puid") = puid
                Cmd.Parameters("@item") = dgv.Rows(i).Cells("item_id").Value
                Cmd.Parameters("@qty") = dgv.Rows(i).Cells("item_qty").Value
                Cmd.Parameters("@price") = dgv.Rows(i).Cells("item_price").Value
                Cmd.Parameters("@disc") = dgv.Rows(i).Cells("item_discount").Value
                Cmd.Parameters("@total") = dgv.Rows(i).Cells("amount").Value
                Cmd.ExecuteNonQuery(sql2)
            Next

            myMsgbox.show("Add New purchase Success!!!", "New Purchase")
            Cmd.Commit()
            Return True
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            Cmd.Rollback()
            Return False
        End Try
    End Function
End Class
